package com.cg.bank.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.concurrent.locks.ReentrantLock;

import com.cg.bank.domain.Transaction;
import com.cg.bank.domain.User;
import com.cg.bank.util.JdbcUtils;
import com.cg.bank.util.MD5;
import com.cg.bank.util.Page;
import com.cg.bank.util.QueryRunner;
import com.cg.bank.util.ScalarHandler;
import com.cg.bank.util.TransactionListHandler;
import com.cg.bank.util.UserBeanHandler;
import com.cg.bank.util.UserBeanListHandler;

public class BankDaoMysqlImpl implements BankDao {
	private static final ReentrantLock LOCK= new ReentrantLock();
	
	public BankDaoMysqlImpl() {
	}

	/**
	 * 添加用户
	 *
	 * @param user 用户信息
	 */
	@Override
	public void addUser(User user) {
		Connection connection = null;
		QueryRunner queryRunner = new QueryRunner();// 获取数据库查询操作器
		String sql = "insert into `user` (account,password,mail,createTime) " + "values(?,?,?,NOW())";
		// 存储密码使用MD5加密(不修改密码时不需加密)
		Object[] params = {user.getAccount(), MD5.getMD5(user.getPassword()), user.getMail()};
		try {
			// 从JdbcUtil获得连接池中的普通/事务连接
			connection = JdbcUtils.getConnection();
			queryRunner.update(connection, sql, params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {// 委托JdbcUtil释放连接，此操作将自动判断是否为事务连接
				JdbcUtils.releaseConnection(connection);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * 添加交易信息
	 *
	 * @param transaction 交易信息
	 */
	@Override
	public void addTransactionRecord(Transaction transaction) {
		Connection connection = null;
		QueryRunner queryRunner = new QueryRunner();
		String sql = "insert into transaction(accountID,time,type,money,detail,balance) values(?,now(),?,?,?,?)";
		Object[] params = { transaction.getUser().getId(), transaction.getType(), transaction.getMoney(),
				transaction.getDetail(), transaction.getBalance() };
		try {
			connection = JdbcUtils.getConnection();
			queryRunner.update(connection, sql, params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				JdbcUtils.releaseConnection(connection);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * 更新用户信息
	 *
	 * @param user 需更新的用户信息
	 */
	@Override
	public void updateUserInformation(User user) {
		Connection connection = null;
		QueryRunner queryRunner = new QueryRunner();
		String password = user.getPassword();
		if (password.length() != 32) {
			password = MD5.getMD5(password);
		}
		System.out.println(user);
		String sql = "update user set userName=?,password=?,age=?,sex=? where id=?";
		Object[] params = { user.getUserName(), password, user.getAge(), user.getSex(), user.getId() };
		try {
			connection = JdbcUtils.getConnection();
			queryRunner.update(connection, sql, params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				JdbcUtils.releaseConnection(connection);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * 更新交易信息
	 *
	 * @param transaction
	 */
	@Override
	public void updateTransactionRecord(Transaction transaction) {
		Connection connection = null;
		QueryRunner queryRunner = new QueryRunner();
		String sql = "update transaction set time=now(),type=?,money=?,detail=?,balance=? where id=?";
		Object[] params = { transaction.getType(), transaction.getMoney(), transaction.getDetail(),
				transaction.getBalance(), transaction.getId() };
		try {
			connection = JdbcUtils.getConnection();
			queryRunner.update(connection, sql, params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				JdbcUtils.releaseConnection(connection);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * 更新用户余额 此操作默认增加用户余额
	 *
	 * @param accountID 账户ID
	 * @param money 更新余额
	 */
	@Override
	public void updateBalance(int accountID, double money) {
		Connection connection = null;
		QueryRunner queryRunner = new QueryRunner();
		// 更新账户余额默认为添加余额操作 注意此sql语句并不是原子操作(可被拆分)  [此操作将在下一版本中更新]
		String sql = "update user set balance=balance+? where id=?";
		Object[] params = { money, accountID };
		try {
			connection = JdbcUtils.getConnection();
			// 上锁
			LOCK.lock();
			queryRunner.update(connection, sql, params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			// 释放锁
			LOCK.unlock();
			try {
				JdbcUtils.releaseConnection(connection);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * 删除用户 请谨慎操作(应该为此操作开启事务)
	 *
	 * @param accountID 删除的账户
	 */
	@Override
	public void deleteUserByAccount(int accountID) {
		Connection connection = null;
		QueryRunner queryRunner = new QueryRunner();
		String sql = "delete from user where id=?";
		Object[] params = { accountID };
		try {
			connection = JdbcUtils.getConnection();
			queryRunner.update(connection, sql, params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				JdbcUtils.releaseConnection(connection);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * 根据账户查询账户ID信息
	 *
	 * @param accountID 查询信息的账户
	 * @return
	 */
	@Override
	public User getUserById(int accountID) {
		return getUser(accountID, null);
	}

	/**
	 * 根据账户查询用户信息
	 *
	 * @param account
	 * @return
	 */
	@Override
	public User getUserByAccount(String account) {
		return getUser(-1, account);
	}

	/**
	 * 查询用户信息
	 * <p>根据账户ID或则账户<p/>
	 * 按需选择，其他值使用默认值
	 * <p>
	 * 例如： 根据ID查询 getUser(id, null)
	 * </p>
	 *
	 * @param accountID 根据ID查询
	 * @param account 根据账户查询
	 * @return 用户信息，不存在返回null
	 */
	protected User getUser(int accountID, String account) {
		Connection connection = null;
		QueryRunner queryRunner = new QueryRunner();
		String sql;
		Object[] params = new Object[1];
		if (accountID != -1) {
			sql = "select * from user where id=?";
			params[0] = accountID;
		} else if (account != null) {
			sql = "select * from user where account=?";
			params[0] = account;
		} else {
			throw new IllegalArgumentException("非法查询参数");
		}
		try {
			connection = JdbcUtils.getConnection();
			return queryRunner.query(connection, sql, new UserBeanHandler(), params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				JdbcUtils.releaseConnection(connection);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * 获取所有用户信息
	 *
	 * @return
	 */
	@Override
	public List<User> getAllUsers() {
		Connection connection = null;
		QueryRunner queryRunner = new QueryRunner();
		String sql = "select id, account,userName,balance,age,sex,createTime from `user`";
		try {
			connection = JdbcUtils.getConnection();
			return queryRunner.query(connection, sql, new UserBeanListHandler());
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				JdbcUtils.releaseConnection(connection);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}
	
	/**
	 * 获取账户余额
	 *
	 * @param accountID 查询的账户
	 * @return
	 */
	@Override
	public double getBalanceByAccount(int accountID) {
		Connection connection = null;
		QueryRunner queryRunner = new QueryRunner();
		String sql = "select balance from `user` where id=?";
		Object[] params = { accountID };
		try {
			connection = JdbcUtils.getConnection();
			User user = queryRunner.query(connection, sql, new UserBeanHandler(), params);
			return user.getBalance();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				JdbcUtils.releaseConnection(connection);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * 获取指定用户交易记录
	 *
	 * @param accountID 需交易查询记录账户
	 * @return
	 */
	@Override
	public List<Transaction> getTransactionRecordByAccount(int accountID, Page page) {
		Connection connection = null;
		QueryRunner queryRunner = new QueryRunner();
		String sql = "select t.id,u.account,u.userName,t.time,t.type,t.money,t.detail,t.balance"
				+ " from transaction t, user u" + " where t.accountID = u.id and u.id=? limit ?,?";
		Object[] params = { accountID, page.getStartno(), page.getPageSize() };
		try {
			connection = JdbcUtils.getConnection();
			return queryRunner.query(connection, sql, new TransactionListHandler(), params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				JdbcUtils.releaseConnection(connection);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * 获取所有用户交易记录
	 *
	 * @return
	 */
	@Override
	public List<Transaction> getAllTransactionRecord(Page page) {
		Connection connection = null;
		QueryRunner queryRunner = new QueryRunner();
		// 此操作默认查询10条记录
		String sql = "select t.id,u.account,u.userName,t.time,t.type,t.money,t.detail,t.balance"
				+ " from transaction t, user u" + " where t.accountID = u.id limit ?,?";
		Object[] params = { page.getStartno(), page.getPageSize() };
		try {
			connection = JdbcUtils.getConnection();
			return queryRunner.query(connection, sql, new TransactionListHandler(), params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				JdbcUtils.releaseConnection(connection);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * 获取指定用户交易记录总数
	 * 
	 * @param accountID 用户ID
	 * @return 交易记录结果集
	 */
	@Override
	public int getTransactionCountsByAccount(int accountID) {
		Connection connection = null;
		QueryRunner queryRunner = new QueryRunner();
		String sql = "select count(*) from transaction where accountID=? group by accountID";
		Object[] params = { accountID };
		try {
			connection = JdbcUtils.getConnection();
			Number count = queryRunner.query(connection, sql, new ScalarHandler<>(), params);
			// count可能为0
			if (count==null){
				return 0;
			}
			return count.intValue();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				JdbcUtils.releaseConnection(connection);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}

	}

	/**
	 * 获取所有交易记录总数
	 * 
	 * @return 交易记录结果集
	 */
	@Override
	public int getTransactionCounts() {
		Connection connection = null;
		QueryRunner queryRunner = new QueryRunner();
		String sql = "select count(*) from transaction";
		try {
			connection = JdbcUtils.getConnection();
			Number count = queryRunner.query(connection, sql, new ScalarHandler<>());
			// count可能为0
			if (count==null){
				return 0;
			}
			return count.intValue();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				JdbcUtils.releaseConnection(connection);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}
	
	// MORE OPERATIONS...
}
